package com.mkyong;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.util.Calendar;
import java.util.Date;
import java.util.UUID;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.springframework.util.StringUtils;

public class ExcelUtils{
	public static HSSFWorkbook hssfWorkbook;
	public static HSSFSheet sheet;
	private static HSSFRow row;
	private static HSSFCell cell;
	
	public static void init() throws Exception{
		if(hssfWorkbook == null)
		 hssfWorkbook = new HSSFWorkbook();
	}
	
	public static void createSheet(String sheetname){
		if(hssfWorkbook != null){
			if(sheet == null)
				sheet = hssfWorkbook.createSheet(sheetname);
			else{
				if(hssfWorkbook.getSheet(sheetname) == null){
					sheet = hssfWorkbook.createSheet(sheetname);
				}
			}
			
		}
	}
	
	public static void insertTitle(int rowNum,int colNum,Object data){
		if(row == null){
			row = sheet.createRow(rowNum);
			row.setHeight((short) 500);
		
		}
		cell = row.createCell(colNum);
		HSSFCellStyle cellStyle = hssfWorkbook.createCellStyle();
		HSSFFont font = hssfWorkbook.createFont();
		font.setBold(true);
		font.setColor(HSSFColor.BLUE.index);
		//font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
		cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
		cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
		cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
		cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
		cellStyle.setFont(font);
		cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		cellStyle.setAlignment(HSSFCellStyle.VERTICAL_CENTER);
		cellStyle.setFillBackgroundColor(HSSFColor.AQUA.index);
		cellStyle.setWrapText(true);
		cell.setCellStyle(cellStyle);
		setCellData(data, cell);
		
	}
	
	public static void insertData(int rowNum,int colNum,Object data){
		if(row == null){
			row = sheet.createRow(rowNum);
		}else{
			if(rowNum != row.getRowNum()){
				row = sheet.createRow(rowNum);
			}
		}
		row.setHeight((short) 500);
		cell = row.createCell(colNum);
		HSSFCellStyle cellStyle = hssfWorkbook.createCellStyle();
		cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
		cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
		cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
		cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
		cell.setCellStyle(cellStyle);
		setCellData(data, cell);
	}
	
	public static void output(String path,String filename) throws Exception{
		if(path == null){
			return;
		}
		if(StringUtils.isEmpty(filename)){
			filename = UUID.randomUUID().toString().replaceAll("-", "");
		}
		File f = new File(path);
		if(!f.exists()){
			f.mkdirs();
		}
		FileOutputStream fos = new FileOutputStream(path+"/"+filename,true);
		hssfWorkbook.write(fos);
		fos.flush();
		fos.close();
		hssfWorkbook.close();
	}

	private static void setCellData(Object data, HSSFCell cell) {
		if(data instanceof String){
			cell.setCellValue((String)data);
		}else if(data instanceof Integer || data instanceof Long ){
			cell.setCellValue(String.valueOf(data));
		}else if(data instanceof Double){
			cell.setCellValue((Double)data);
		}else if(data instanceof Date){
			cell.setCellValue((Date)data);
		}else if (data instanceof Boolean){
			cell.setCellValue((Boolean)data);
		}else{
			cell.setCellValue(String.valueOf(data));
		}
		
	}
}
